/**
 * 菜单模块数据库操作文件
 */

const { pool, queryFirst, query } = require('./common');

/**
 * 验证路径
 * @param {*} menu 要验证的菜单信息
 * @returns 
 */
const validPath = (menu) => queryFirst('SELECT 1 a FROM menus WHERE path = ?', [menu.path]);

/**
 * 添加菜单
 * @param {*} menu 要添加的菜单信息
 */
const addMenu = (menu) => pool.query('INSERT INTO menus SET ?', menu);

/**
 * 修改菜单
 * @param {*} menu 要修改的菜单对象
 */
const updateMenu = async (menu) => {
    const conn = await pool.getConnection();
    try {
        const [[result]] = await conn.query('SELECT 1 FROM menus WHERE path = ? AND id <> ?', [menu.path, menu.id]);
        if (result) {
            return 0;
        }
        const { id } = menu;
        delete menu.id;
        await conn.query('UPDATE menus SET ? WHERE id = ?', [menu, id]);
        return 1;
    }
    finally {
        conn.release();
    }
}

/**
 * 根据ID删除菜单
 * @param {*} id 
 * @returns 
 */
const deleteMenuById = async (id) => {
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();
        await conn.query('DELETE FROM role_menu WHERE menu_id = ?', [id]);
        await conn.query('DELETE FROM menus WHERE id = ? OR pid = ?', [id, id]);
        await conn.commit();
    }
    catch (error) {
        await conn.rollback();
        throw error;
    }
    finally {
        conn.release();
    }
}

/**
 * 获取菜单列表
 * @returns 
 */
const getMenuList = () => query('SELECT * FROM menus ORDER BY sort');

/**
 * 根据教师ID获取菜单信息
 * @param {*} id 
 * @returns 
 */
const getMenuListByTeacherId = (id) => query(`SELECT * FROM (SELECT DISTINCT m.* FROM teacher_role t
    INNER JOIN role_menu r on t.role_id = r.role_id
    INNER JOIN menus m on r.menu_id = m.id
    WHERE t.teacher_id = ? AND m.pid > 0
    ORDER BY m.sort) t
    UNION
    SELECT * FROM menus WHERE pid = 0 ORDER BY sort`, [id]);

/**
 * 根据角色ID获取菜单列表
 * @param {*} id 角色ID
 * @returns 
 */
const getMenusByRoleId = id => query('SELECT menu_id FROM role_menu WHERE role_id = ?', [id]);

module.exports = {
    addMenu,
    validPath,
    updateMenu,
    getMenuList,
    deleteMenuById,
    getMenusByRoleId,
    getMenuListByTeacherId
}